﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;
using AG.Utilities;

namespace SQLServerHealthCheck.SQLServer.IssueTypes
{
    public class DatabaseCheckLogFileSizetoData : IssueTypeCheckBase
    {
        public DatabaseCheckLogFileSizetoData()
        {
            IssueTypeId = 3005;
        }

        public override void Check()
        {
            try
            {

                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
                Issue issue;

                // We create 2 Queries to split the table in 2 , in order to compare the vales of there File SIZES
                var qrylog = from df in dc.DatabaseFiles

                             where df.FileGroup.Contains("LOG")

                             select df;

                var qryprim = from df in dc.DatabaseFiles

                              where df.FileGroup.Contains("PRIMARY")
                              && (df.Database.DatabaseName != "distribution") && (df.Database.DatabaseName != "master") && (df.Database.DatabaseName != "msdb") && (df.Database.DatabaseName != "tempdb")

                              select df;

                var qryjoin = from l in qrylog
                              join p in qryprim on l.DatabaseID equals p.DatabaseID
                              where l.SizeInMb > 2* p.SizeInMb

                              select new
                              {
                                  DatabaseId = l.DatabaseID,
                                  DatabaseName = l.Database.DatabaseName,
                                  LogSize = l.SizeInMb,
                                  DataFileSize = p.SizeInMb
                              };
                // Iterate through the Linq Query 
                foreach (var item in qryjoin)
                {
                    int databaseId = item.DatabaseId;
                    //Get Issuetype object

                    issue = new Issue();

                    //Get Issuetype object  And Create a new  Global issue object 
                    IssueType issueType = dc.IssueTypes.FirstOrDefault(i => i.IssueTypeId == IssueTypeId);

                    issue.IssueType = issueType;
                    issue.TargetId = item.DatabaseId;
                    issue.Description = issueType.Description.Replace("{Database}", item.DatabaseName).Replace("{LogSize}", item.LogSize.ToString()).Replace("{DataSize}", item.DataFileSize.ToString());

                    dc.Issues.InsertOnSubmit(issue);
                }

                dc.SubmitChanges();


            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }

    }
}
